package DBLayer;

import Model.Product;
import Model.Supplier;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * Database handling for products
 * 
 * @author Group 1
 * @version 2011.03.31.
 */

public class DbProduct implements DbInterfaceProduct {
    private Connection db_con;

    public DbProduct() {
        db_con = DbConnection.getInstance().getDBcon();
    }

    public ArrayList<Product> getAllProducts() {
        return miscWhere("");
    }

    public Product searchById(String id) {
        String wClause = "id = " + id + "";
        return singleWhere(wClause);
    }

	public Product searchByName(String name) {
		String wClause = "name = '"+name+"'";
		return singleWhere(wClause);
	}

    public ArrayList<Product> searchBySupplierId(String id) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public ArrayList<Product> searchByCountry(String country) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public int insertProduct(Product prod) {
        int result = -1;
        String query = "INSERT INTO products(name, supplier_id, purchase_price, sale_price, rent_price, country, min_stock) VALUES"
                + "('" + prod.getName() + "', '"+ prod.getSupplier().getId() +"', '" + prod.getPurchasePrice() + "', '" + prod.getSalesPrice()
                + "', '" + prod.getRentPrice() + "', '" + prod.getCountry() + "', '" + prod.getMinStock() + "')";
        System.out.println("insert: " + query);
        try {
            Statement stmt = db_con.createStatement();
            stmt.setQueryTimeout(5);
            result = stmt.executeUpdate(query);
            stmt.close();
        } catch (Exception ex) {
            System.out.println("Insert exception in products db: " + ex);
        }
        return result;
    }

    public int updateProduct(Product prod) {
        int result = -1;

        String query = "UPDATE products SET ";
        if (prod.getName() != null) {
            query += "name = '" + prod.getName() + "',";
        }
		if (prod.getSupplier() != null && prod.getSupplier().getId() != 0)
			query += "supplier = '" + prod.getSupplier().getId() + "',";
		if (prod.getCountry() != null) {
            query += "country = '" + prod.getCountry() + "',";
        }
        if (prod.getPurchasePrice() != 0) {
            query += "purchase_price = '" + prod.getPurchasePrice() + "',";
        }
        if (prod.getSalesPrice() != 0) {
            query += "sale_price = '" + prod.getSalesPrice() + "',";
        }
        if (prod.getCountry() != null) {
            query += "rent_price = '" + prod.getRentPrice() + "',";
        }
        if (prod.getMinStock() != 0) {
            query += "min_stock = '" + prod.getMinStock() + "',";
        }
        if (query.endsWith(",")) {
            query = query.substring(0, query.length() - 1);
        }
        query += " WHERE id = " + prod.getId();
        System.out.println("Update query:" + query);
        try { // update product
            Statement stmt = db_con.createStatement();
            stmt.setQueryTimeout(5);
            result = stmt.executeUpdate(query);

            stmt.close();
        }//try end
        catch (Exception ex) {
            System.out.println("Update exception in products db: " + ex);
        }
        return result;
    }

    public int deleteProduct(String id) {
        int result = -1;

        String query = "DELETE FROM products WHERE id = '"
                + id + "'";
        System.out.println("delete: " + query);
        try { // delete from products
            Statement stmt = db_con.createStatement();
            stmt.setQueryTimeout(5);
            result = stmt.executeUpdate(query);
            stmt.close();
        }//try end
        catch (Exception ex) {
            System.out.println("Delete exception in products db: " + ex);
        }
        return result;
    }

    /*
     * DB Stuff
     */
    private ArrayList<Product> miscWhere(String wClause) {
        ResultSet results;
        ArrayList<Product> list = new ArrayList<Product>();

        String query = buildQuery(wClause);

        try { // read the product from the database
            Statement stmt = db_con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            int snr = 0;
            while (results.next()) {
                Product prodObj = new Product();
                prodObj = buildProduct(results);
                list.add(prodObj);
            }//end while
            stmt.close();
        } catch (Exception e) {
            System.out.println("Query exception - select: " + e);
            e.printStackTrace();
        }
        return (list.size() > 0 ? list : null);
    }

    //Single WHERE is used when we only select one product
    private Product singleWhere(String wClause) {
        ResultSet results;
        Product prodObj = null;
        String query = buildQuery(wClause);
        System.out.println(query);
        try { // read the product from the database
            Statement stmt = db_con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);
            if (results.next()) {
                prodObj = buildProduct(results);
                stmt.close();
            }

        }//end try
        catch (Exception e) {
            System.out.println("Query exception: " + e);
        }
        return prodObj;
    }

    //method to build the query
    private String buildQuery(String wClause) {
        String query = "SELECT * FROM products";

        if (wClause.length() > 0) {
            query = query + " WHERE " + wClause;
        }

        return query;
    }

    //method to build an product object
    private Product buildProduct(ResultSet results) {
        Product prodObj = new Product();
        try {
            prodObj.setId(Integer.parseInt(results.getString(1)));
            prodObj.setName(results.getString(2));
            prodObj.setSupplier(new Supplier(Integer.parseInt(results.getString(3))));
            prodObj.setPurchasePrice(Double.parseDouble(results.getString(4)));
            prodObj.setSalesPrice(Double.parseDouble(results.getString(5)));
            prodObj.setRentPrice(Double.parseDouble(results.getString(6)));
            prodObj.setCountry(results.getString(7));
            prodObj.setMinStock(Integer.parseInt(results.getString(8)));
        } catch (Exception e) {
            System.out.println("error in building the product object");
			e.printStackTrace();
        }
        return prodObj;
    }

}
